package com.example.demo.Dao;

import com.example.demo.entity.Course;
import com.example.demo.entity.Employee;
import com.example.demo.entity.MC;
import com.example.demo.entity.Member;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Repository
public class FitDao {
    //    通过JdbcTemplate  用sql语句实现功能
    private final JdbcTemplate jt;

    //    带参数的构造函数
    public FitDao(JdbcTemplate jt) {
        this.jt = jt;
    }

    //获取健身卡选项
    public List<Map<String, Object>> getCardOptions() {
        String sql = "SELECT cardName FROM card";
        return jt.queryForList(sql);
    }

    //获取会籍选项
    public List<Map<String, Object>> getHuijiOptions() {
        String sql = "SELECT eeName FROM ee WHERE eeType = '会籍顾问'";
        return jt.queryForList(sql);
    }

    // 添加新会员
    public void addMember(Member member) {
        // 生成会员号
        LocalDateTime now = LocalDateTime.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyMMddHHmmss");
        String memberNum = formatter.format(now);
        member.setMemberNum(memberNum);

        String sql = "INSERT INTO membership (memberNum, memberName, memberSex, phoneNum, card, huiji) " +
                "VALUES (?, ?, ?, ?, ?, ?)";
        jt.update(sql,
                member.getMemberNum(),
                member.getMemberName(),
                member.getMemberSex(),
                member.getPhoneNum(),
                member.getCard(),
                member.getHuiji());
    }

    // 会员管理 获取全部会员列表
    public List<Member> getAllMembers() {
        String sql = "SELECT * FROM membership";
        return jt.query(sql, (rs, rowNum) -> {
            Member member = new Member();
            member.setMemberNum(rs.getString("memberNum"));
            member.setMemberName(rs.getString("memberName"));
            member.setMemberSex(rs.getString("memberSex"));
            member.setPhoneNum(rs.getString("phoneNum"));
            member.setCard(rs.getString("card"));
            member.setHuiji(rs.getString("huiji"));
            return member;
        });
    }

    //查询一个会员的会员信息
    public List<Member> getMember(String memberNum) {
        String sql = "SELECT * FROM membership WHERE memberNum = ?";
        return jt.query(sql, new Object[]{memberNum}, (rs, rowNum) -> {
            Member member = new Member();
            member.setMemberNum(rs.getString("memberNum"));
            member.setMemberName(rs.getString("memberName"));
            member.setMemberSex(rs.getString("memberSex"));
            member.setPhoneNum(rs.getString("phoneNum"));
            member.setCard(rs.getString("card"));
            member.setHuiji(rs.getString("huiji"));
            return member;
        });
    }

    //删除指定会员
    public int deleteMember(String memberNum) {
        String sql = "DELETE FROM membership WHERE memberNum = ?";
        return jt.update(sql, memberNum);
    }

    //更新会员
    public int updateMember(Member member) {
        String sql = "UPDATE membership SET memberName = ?, memberSex = ?, phoneNum = ?, card = ?, huiji = ? WHERE memberNum = ?";
        return jt.update(sql,
                member.getMemberName(),
                member.getMemberSex(),
                member.getPhoneNum(),
                member.getCard(),
                member.getHuiji(),
                member.getMemberNum());
    }

    // 课程管理 获取全部课程信息
    public List<Course> getAllCourses() {
        String sql = "SELECT * FROM course";
        return jt.query(sql, (rs, rowNum) -> {
            Course course = new Course();
            course.setCourseNum(rs.getString("courseNum"));
            course.setCourseName(rs.getString("courseName"));
            course.setCoach(rs.getString("coach"));
            course.setPrice(rs.getDouble("price"));
            return course;
        });
    }

    // 添加课程
    public void addCourse(Course course) {
        // 生成会员号
        LocalDateTime now = LocalDateTime.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("SSssHHddMMyyyy");
        String courseNum = formatter.format(now);
        course.setCourseNum(courseNum);

        String sql = "INSERT INTO course (courseNum, courseName, coach, price) " +
                "VALUES (?, ?, ?, ?)";
        jt.update(sql,
                course.getCourseNum(),
                course.getCourseName(),
                course.getCoach(),
                course.getPrice());
    }

    // 查询课程名称对应的的课程号
    public List<String> getCourseNum(String courseName) {
        String sql = "SELECT courseNum FROM course WHERE courseName = ?";
        return jt.query(sql, new Object[]{courseName}, (rs, rowNum) -> {
            return rs.getString("courseNum");
        });
    }

    //添加报课信息
    public int addMC(MC mc) {
// 使用date类型自动生成dt
        Date dt = new Date();
        mc.setDt(dt);
        String sql = "INSERT INTO mc (memberNum, memberName, courseNum, courseName, dt) VALUES (?, ?, ?, ?, ?)";
        return jt.update(sql,
                mc.getMemberNum(),
                mc.getMemberName(),
                mc.getCourseNum(),
                mc.getCourseName(),
                mc.getDt());
    }

    //     按类型查询所有员工
    public List<Employee> getEmployeesByType(String type) {
        String sql = "SELECT * FROM ee WHERE eeType = ?";
        return jt.query(sql, new Object[]{type}, (rs, rowNum) -> {
            Employee emp = new Employee();
            emp.setEeNum(rs.getString("eeNum"));
            emp.setEeName(rs.getString("eeName"));
            emp.setEeType(rs.getString("eeType"));
            emp.setEePhone(rs.getString("eePhone"));
            emp.setPerform(rs.getDouble("perform"));
            return emp;
        });
    }

    //添加员工
    public int addEmployee(Employee employee) {
        LocalDateTime now = LocalDateTime.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyMMddHHmmssSS");
        String eeNum = formatter.format(now);
        employee.setEeNum(eeNum);
        employee.setPerform(0);
        String sql = "INSERT INTO ee (eeNum, eeName, eeType, eePhone, perform) VALUES (?, ?, ?, ?, ?)";
        return jt.update(sql,
                employee.getEeNum(),
                employee.getEeName(),
                employee.getEeType(),
                employee.getEePhone(),
                employee.getPerform());
    }

    //更新员工信息
    public int updateEmployee(Employee employee) {
        String sql = "UPDATE ee SET eeName = ?, eePhone = ?, perform = ? WHERE eeNum = ?";
        return jt.update(sql,
                employee.getEeName(),
                employee.getEePhone(),
                employee.getPerform(),
                employee.getEeNum());
    }

    //删除员工
    public int deleteEmployee(String eeNum) {
        String sql = "DELETE FROM ee WHERE eeNum = ?";
        return jt.update(sql, eeNum);
    }

    public List<MC> getAllMC() {
        String sql = "SELECT * FROM MC";
        return jt.query(sql, (rs, rowNum) -> {
            MC mc = new MC();
            mc.setMemberNum(rs.getString("memberNum"));
            mc.setMemberName(rs.getString("memberName"));
            mc.setCourseNum(rs.getString("courseNum"));
            mc.setCourseName(rs.getString("courseName"));
            mc.setDt(rs.getDate("dt"));
            return mc;
        });
    }

    public int deleteMC(String memberNum, String courseNum) {
        String sql = "DELETE FROM mc WHERE memberNum = ? AND courseNum = ?";
        return jt.update(sql, memberNum, courseNum);

    }

    //删除课程
    public int deleteCourse(String courseNum) {
        String sql = "DELETE FROM course WHERE courseNum = ?";
        return jt.update(sql, courseNum);
    }

    //    课程办理
//    public int buyCourse(Course course) {
//        String sql = "INSERT INTO course (courseNum, courseName,) VALUES (?, ?)";
//        return jt.update(sql, course.getCourseNum(), course.getCourseName());
//    }


}
